﻿using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Data.SqlClient;
using System.Linq;
using System.Linq.Dynamic;
using System.Transactions;
using PagedList;
using Sovigaz.DataProvider;
using Utilities;

namespace Sovigaz.Permission.Repositories
{
    public class ProductRepository : GenericRepository
    {
        public ProductRepository()
            : base("ProductID")
        {
        }

        public ICollection<ProductComplex> Products { get; set; }

        public void Delete(int id)
        {
            var record = GetByID(id);
            if (record != null)
                record.IsDeleted = true;

            _context.SaveChanges();
        }

        public void Delete(string[] idArr)
        {
            foreach (var id in idArr)
            {
                var role = GetByID(ConvertUtility.IntParse(id));
                if (role != null)
                    role.IsDeleted = true;
            }

            _context.SaveChanges();
        }

        public int Update(Product item)
        {
            var update = new Product();
            using (var scope = new TransactionScope())
            {
                if (item.ProductID > 0)
                {
                    update = GetByID(item.ProductID);
                    if (!Utility.ArrayEqual(update.Tstamp, item.Tstamp))
                        throw ExceptionHelper.CreateHttpResponseException("Dữ liệu đã bị cập nhật bởi người sử dụng khác.");
                }
                update.ProductID = item.ProductID;
                update.LotID = item.LotID;
                update.CategoryID = item.CategoryID;
                update.OwnerID = item.OwnerID;
                update.PartnerID = item.PartnerID;
                update.Barcode = item.Barcode;
                update.ProductCode = item.ProductCode;
                update.Manufacture = item.Manufacture;
                update.Symbol = item.Symbol;
                update.ManufacturedDate = item.ManufacturedDate;
                update.Weight = item.Weight;
                update.Volume = item.Volume;
                update.PressureLoad = item.PressureLoad;
                update.PressureTest = item.PressureTest;
                update.Capacity = item.Capacity;
                update.BoughtDate = item.BoughtDate;
                update.CheckedDate = item.CheckedDate;
                update.AssignedToEnterprise = item.AssignedToEnterprise;
                update.AssignedDate = item.AssignedDate;
                update.ChatXop = item.ChatXop;
                update.Certify = item.Certify;
                update.Description = item.Description;
                update.Expired = item.Expired;
                update.IsEmpty = item.IsEmpty;
                update.IsIndemnify = item.IsIndemnify;
                update.Size = item.Size;
                update.Status = item.Status;

                if (update.ProductID == 0)
                    _context.Products.Add(update);

                _context.SaveChanges();

                scope.Complete();
            }

            _context.Entry(update).Reload();

            return update.ProductID;
        }

        public Product GetByID(int id)
        {
            return _context.Products.FirstOrDefault(p => p.ProductID == id && !p.IsDeleted);
        }

        //public ProductRepository GetList1(string whereClause, string orderBy, string orderDirection, int pageNumber)
        //{
        //    var query = _context.Products.Where(whereClause)
        //        .Join(_context.Categories, p => p.CategoryID, c => c.CategoryID,
        //        (p, c) => new {p,c})
        //        .Join(_context.Tests.DefaultIfEmpty(), pc => pc.p.LastTestID, t => t.TestID, (pc, t) => new ProductComplex()
        //        {
        //            ProductID=pc.p.ProductID,
        //            LotID=pc.p.LotID,
        //            CategoryID=pc.p.CategoryID,
        //            CategoryName=pc.c.CategoryName,
        //            OwnerID=pc.p.OwnerID,
        //            PartnerID=pc.p.PartnerID,
        //            ProductCode=pc.p.ProductCode,
        //            Barcode=pc.p.Barcode,
        //            Manufacture=pc.p.Manufacture,
        //            TestWeight=t.TestWeight,
        //            Weight = pc.p.Weight,
        //            LostWeight=pc.p.Weight-t.TestWeight,
        //            TestVolume=t.TestVolume,
        //            Volume = pc.p.Volume,
        //            Size=pc.p.Size,
        //            PressureLoad = pc.p.PressureLoad,
        //            PressureTest = pc.p.PressureTest,
        //            Capacity = pc.p.Capacity,
        //            Expired=pc.p.Expired,
        //            Status=pc.p.Status,
        //        })                
        //        .OrderBy(String.Format("{0} {1}", orderBy.Trim(), orderDirection.Trim()))
        //        .ToPagedList(pageNumber, DefaultPageSize);

        //    TotalItemCount = query.TotalItemCount;
        //    TotalItemOnPage = query.Count;
        //    PageCount = query.PageCount;
        //    PageNumber = query.PageNumber;
        //    Products = query.ToList();

        //    return this;
        //}

        public ProductRepository GetList(string whereClause, string orderBy, string orderDirection, int pageNumber)
        {
            var query = _context.Products.Where(whereClause)
                .Join(_context.Categories, p => p.CategoryID, c => c.CategoryID,
                (p, c) => new ProductComplex()
                {
                    ProductID = p.ProductID,
                    LotID = p.LotID,
                    CategoryID = p.CategoryID,
                    CategoryName = c.CategoryName,
                    OwnerID = p.OwnerID,
                    PartnerID = p.PartnerID,
                    ProductCode = p.ProductCode,
                    Barcode = p.Barcode,
                    Manufacture = p.Manufacture,
                    Weight = p.Weight,                    
                    Volume = p.Volume,
                    Size = p.Size,
                    PressureLoad = p.PressureLoad,
                    PressureTest = p.PressureTest,
                    Capacity = p.Capacity,
                    Expired = p.Expired,
                    Status = p.Status,
                    TestWeight = _context.Tests.Count(t => t.ProductID == p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestWeight : 0,
                    TestVolume = _context.Tests.Count(t => t.ProductID == p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestVolume : 0,
                    TestedDate = _context.Tests.Count(t => t.ProductID == p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestedDate : DateTime.Now,
                })
                .OrderBy(String.Format("{0} {1}", orderBy.Trim(), orderDirection.Trim()))
                .ToPagedList(pageNumber, DefaultPageSize);

            TotalItemCount = query.TotalItemCount;
            TotalItemOnPage = query.Count;
            PageCount = query.PageCount;
            PageNumber = query.PageNumber;
            Products = query.ToList();

            return this;
        }

        public ProductRepository GetList(string whereClause, string orderBy, string orderDirection, int pageNumber, DateTime expiredfrom, DateTime expiredto)
        {
            var query = _context.Products.Where(whereClause)
                .Join(_context.Categories, p => p.CategoryID, c => c.CategoryID,
                (p, c) => new ProductComplex()
                {
                    ProductID = p.ProductID,
                    LotID = p.LotID,
                    CategoryID = p.CategoryID,
                    CategoryName = c.CategoryName,
                    OwnerID = p.OwnerID,
                    PartnerID = p.PartnerID,
                    ProductCode = p.ProductCode,
                    Barcode = p.Barcode,
                    Manufacture = p.Manufacture,
                    Weight = p.Weight,
                    Volume = p.Volume,
                    Size = p.Size,
                    PressureLoad = p.PressureLoad,
                    PressureTest = p.PressureTest,
                    Capacity = p.Capacity,
                    Expired = p.Expired,
                    Status = p.Status,
                    TestWeight = _context.Tests.Count(t => t.ProductID == p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestWeight : 0,
                    TestVolume = _context.Tests.Count(t => t.ProductID == p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestVolume : 0,
                    TestedDate = _context.Tests.Count(t => t.ProductID == p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestedDate : DateTime.Now,
                })
                .Where(p => p.Expired >= expiredfrom.Date && p.Expired <= expiredto.Date)
                .OrderBy(String.Format("{0} {1}", orderBy.Trim(), orderDirection.Trim()))
                .ToPagedList(pageNumber, DefaultPageSize);

            TotalItemCount = query.TotalItemCount;
            TotalItemOnPage = query.Count;
            PageCount = query.PageCount;
            PageNumber = query.PageNumber;
            Products = query.ToList();

            return this;
        }

        public ProductRepository GetList(string whereClause, string orderBy, string orderDirection, int pageNumber, DateTime expireddate)
        {
            var query = _context.Products.Where(whereClause)
                .Join(_context.Categories, p => p.CategoryID, c => c.CategoryID,
                (p, c) => new ProductComplex()
                {
                    ProductID = p.ProductID,
                    LotID = p.LotID,
                    CategoryID = p.CategoryID,
                    CategoryName = c.CategoryName,
                    OwnerID = p.OwnerID,
                    PartnerID = p.PartnerID,
                    ProductCode = p.ProductCode,
                    Barcode = p.Barcode,
                    Manufacture = p.Manufacture,
                    Weight = p.Weight,
                    Volume = p.Volume,
                    Size = p.Size,
                    PressureLoad = p.PressureLoad,
                    PressureTest = p.PressureTest,
                    Capacity = p.Capacity,
                    Expired = p.Expired,
                    Status = p.Status,
                    TestWeight = _context.Tests.Count(t => t.ProductID == p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestWeight : 0,
                    TestVolume = _context.Tests.Count(t => t.ProductID == p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestVolume : 0,
                    TestedDate = _context.Tests.Count(t => t.ProductID == p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestedDate : DateTime.Now,
                })
                .Where(p => p.Expired <= expireddate.Date)
                .OrderBy(String.Format("{0} {1}", orderBy.Trim(), orderDirection.Trim()))
                .ToPagedList(pageNumber, DefaultPageSize);

            TotalItemCount = query.TotalItemCount;
            TotalItemOnPage = query.Count;
            PageCount = query.PageCount;
            PageNumber = query.PageNumber;
            Products = query.ToList();

            return this;
        }

        public int GetNextIndex()
        {
            if (_context.Products.FirstOrDefault() == null) return 1;
            return _context.Products.Max(p => p.ProductID) + 1;
        }

        public List<ProductComplex> GetListProduct()
        {
            var query = _context.Products.Where(p => !p.IsDeleted)
                .Join(_context.Categories, p => p.CategoryID, c => c.CategoryID,
                (p, c) => new { p, c })
                .Join(_context.Partners, pc => pc.p.OwnerID, pa => pa.PartnerID, (pc, pa) => new ProductComplex()
                {
                    ProductID = pc.p.ProductID,
                    LotID = pc.p.LotID,
                    CategoryID = pc.p.CategoryID,
                    CategoryName = pc.c.CategoryName,
                    OwnerID = pc.p.OwnerID,
                    OwnerName = pa.PartnerName,
                    PartnerID = pc.p.PartnerID,
                    ProductCode = pc.p.ProductCode,
                    Barcode = pc.p.Barcode,
                    Manufacture = pc.p.Manufacture,
                    Weight = pc.p.Weight,
                    Volume = pc.p.Volume,
                    Size = pc.p.Size,
                    PressureLoad = pc.p.PressureLoad,
                    PressureTest = pc.p.PressureTest,
                    Capacity = pc.p.Capacity,
                    Expired = pc.p.Expired,
                    Status = pc.p.Status,
                });
            return query.ToList();
        }

        public List<ProductComplex> GetListProduct(string whereclause)
        {
            var query = _context.Products.Where(whereclause)
                .Join(_context.Categories, p => p.CategoryID, c => c.CategoryID,
                (p, c) => new { p, c })
                .Join(_context.Partners, pc => pc.p.OwnerID, pa => pa.PartnerID, (pc, pa) => new ProductComplex()
                {
                    ProductID = pc.p.ProductID,
                    LotID = pc.p.LotID,
                    CategoryID = pc.p.CategoryID,
                    CategoryName = pc.c.CategoryName,
                    OwnerID = pc.p.OwnerID,
                    OwnerName = pa.PartnerName,
                    PartnerID = pc.p.PartnerID,
                    ProductCode = pc.p.ProductCode,
                    Barcode = pc.p.Barcode,
                    Manufacture = pc.p.Manufacture,
                    Weight = pc.p.Weight,
                    Volume = pc.p.Volume,
                    Size = pc.p.Size,
                    PressureLoad = pc.p.PressureLoad,
                    PressureTest = pc.p.PressureTest,
                    Capacity = pc.p.Capacity,
                    Expired = pc.p.Expired,
                    Status = pc.p.Status,
                    IsDeleted = pc.p.IsDeleted,
                    TestWeight = _context.Tests.Count(t => t.ProductID == pc.p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == pc.p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestWeight : 0,
                    TestVolume = _context.Tests.Count(t => t.ProductID == pc.p.ProductID) > 0 ? _context.Tests.Where(t => t.ProductID == pc.p.ProductID).OrderByDescending(t => t.TestID).FirstOrDefault().TestVolume : 0,
                });
                
            return query.ToList();
        }

        public int GetCount(string whereclause)
        {
            return _context.Products.Where(whereclause).ToList().Count;
        }
    }
}
